from selenium import webdriver
import time
import requests
import re
import paho.mqtt.client as mqtt
import pymysql
import logging


class upgrade:
    # def __init__(self):
    #     upgrade.db(self)

    def db(self):
        host = "gz-cdb-5gusw5gx.sql.tencentcdb.com"
        username = "wangzhijing"
        password = "wangzhijing"
        dbname = "hardware_open"
        port = 63044
        db = pymysql.connect(host, username, password, dbname, port)
        cursor = db.cursor()
        version = str(input("请输入要查询的version："))
        appid = str(input("请输入要查询的appid："))
        type = str(input("请输入要查询的type："))
        sql = "SELECT sn FROM hardware_list WHERE version='" + version + "'" + " and appid='" + appid + "'" + "and type='" + type + "'"
        # print(sql)
        cursor.execute(sql)
        results = cursor.fetchall()
        # print(results)
        f = open(r'C:\Users\Administrator\Desktop\1.txt', 'w')
        for result in results:
            r = ""
            content = r.join(result)
            f.writelines(content + '\n')
        f.close()
        db.close()
        return version

    def openFile(self, version):
        # 打开路径下的文件，权限设为只读
        f = open(r'C:\Users\Administrator\Desktop\1.txt', 'r')  # 电脑新建文件的路径，可以随意设置
        strings = f.read()
        sns = strings.split('\n')
        upgrade.onlineCheck(self, sns, version)
        f.close()

    # def log(self):
    #     log = logging.StreamHandler().setLevel(logging.DEBUG)

    def onlineCheck(self, snsn, version):
        online_url = 'http://admin-open.dairphone.cn/default/a1s/is_online'
        headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; '
                                 'Win64; x64) '
                                 'AppleWebKit/537.36 ( '
                                 'KHTML, like Gecko) '
                                 'Chrome/75.0.3770.100 '
                                 'Safari/537.36 ',
                   "Content-Type": 'application/x-www-form'
                                   '-urlencoded;charset '
                                   '=UTF-8',
                   'Host': 'admin-open.dairphone.cn',
                   'Referer': 'http://admin-open.dairphone.cn/default/a1s/is_online'}
        # 通过selenium登录开放平台
        driver = webdriver.Chrome()
        url = 'http://admin-open.dairphone.cn/default/staticpage/login'
        driver.get(url)  # 访问开放平台
        time.sleep(1)
        # 发送账号和密码再登录
        driver.find_element_by_xpath('/html/body/div/div[2]/form/div[1]/input').send_keys('111000')
        driver.find_element_by_xpath('/html/body/div/div[2]/form/div[2]/input').send_keys('123')
        driver.find_element_by_xpath('/html/body/div/div[2]/form/div[3]/button').click()
        time.sleep(1)
        # 获取cookie
        cookie = driver.get_cookies()
        # print(cookie)
        driver.close()  # 关闭浏览器
        # 以name+value格式保存cookie
        for item in cookie:
            cookie = [item["name"] + "=" + item["value"]]
        # 获取和打印cookies
        cookies = ';'.join(item for item in cookie)
        # print(cookies)
        headers['cookie'] = cookies
        # print(headers)
        # global s
        # url = 'http://admin-open.dairphone.cn/default/staticpage/login'
        # html = requests.get(url)  # 访问开放平台
        # strings = []
        # if html.status_code == 200:
        #     cookie = str(html.cookies)
        #     for i in range(len(cookie)):
        #         strings.append(cookie[i])
        # cookies = ''.join(strings[27:67])
        # print(cookies)
        # while True:
        num = 0
        for sn in snsn:  # 遍历查询列表中每个sn的在线情况
            # 访问网址，输入参数sn，并返回页面
            response = requests.get(online_url, params={'sn': sn}, headers=headers)
            # 将页面数据转换成文本
            result = response.text
            # print(result)
            # 设置正则筛选出关键字符
            # a = re.compile(r"""tr\s*data-id="(\d+)[\s\S]*?<span>(.线)""").findall(result)
            c = re.findall('data-id="(.*?)"', result, re.S)[0].strip()
            d = re.findall('<span>(.线)</span>', result, re.S)[0].strip()
            # 在返回的文本中设置正则，并打印出筛选后的数据
            # print(f"{c} {d}")
            a = c + d
            # print(a)
            if "在线" in a:
                print(a)
                num += 1
                file1 = open(r'C:\Users\Administrator\Desktop\2.txt', 'a')  # 电脑新建文件的路径，可以随意设置
                file1.writelines(a + '\n')  # 逐行写入2.txt中
                file1.close()
                # logging.debug(f"共筛选出{num}个在线设备")

        print(f"V{version}共筛选出{num}个在线设备")
        # 创建2.txt，权限设为写
        # file1 = open(r'D:\2.txt', 'w')  # 电脑新建文件的路径，可以随意设置
        # for b in a:
        #     # print(", ".join(map(str, b)))
        #     # x = list(b)
        #     # x.sort(key=在线, reverse=False)
        #     x = ",".join(b)  # 生成新的字符串
        #     print(x)
        #     # print("".join(b))
        # file1.writelines(x + '\n')  # 逐行写入2.txt中
        # file1.close()
        # #  打开2.txt读取之前写入的字符串，权限设为仅读
        # file2 = open(r'D:\2.txt', 'r')  # 电脑新建文件的路径，可以随意设置
        # result2 = file2.read()
        # pattern2 = re.compile(r".*?在线")  # 在打开的文件中匹配带有在线及在线前面的sn
        # r2 = pattern2.findall(result2)
        # print(r2)
        # file2.close()
        # if not r2:  # 增加判断，如果没有含有在线字眼，则终止往下走，继续回到循环，重新输入sn查询是否在线
        #     time.sleep(0.5)  # 增加休眠，便于查看
        #     continue
        # else:  # 判断如果存在在线字眼，遍历打印和生成这些带有在线字眼的sn
        #     for r1 in r2:
        #         # print(r1.split(",", 1))
        #         r3 = r1[:14]  # 将字符串前14位数字截取下来作为r3参数传递给test()函数
        #         # print(r3)
        #         upgrade.mqttPush(self, r3, snsn)  # 传递r3（即所需的在线sn）
        #         continue
        #     time.sleep(30)  # 推送完成一次，休眠30s等待推送下一个sn
    #
    # def mqttPush(self, sn, key):
    #     print("需要推送升级的sn:", sn)
    #     client = mqtt.Client()
    #     client.connect('so2.airphone.vip', 1883, 5)
    #     #  通过mqtt推送升级指令，根据新旧版本及订阅主题不一致，可以更改主题和json
    #     # client.publish('/1007/' + sn + '/user/iot/cmd', payload='{"cmd":"upgrade","value":"http://down1.cmfspay.com'
    #     #                                                         '/download/C01Aupgrade/C01A_V1.4.6.apk"}', qos=0)
    #     client.publish('/1007/' + sn + '/user/iot/cmd', payload='{"cmd":"upgrade","value":"http://down1.cmfspay.com'
    #                                                             '/download/C01Aupgrade/C01A_V1.4.6.apk"}', qos=0)
    #     print(sn + "推送升级成功，注意设备是否升级，请稍等···")
    #     # 增加判断，如果已经推送的sn，则移除，避免重复推送
    #     if sn in key:
    #         key.remove(sn)
    # print(key)


if __name__ == '__main__':
    publishUpgrade = upgrade()
    v = publishUpgrade.db()
    publishUpgrade.openFile(v)
